﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO.Compression;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static log4net.Appender.RollingFileAppender;

namespace CloudApiServer.Services
{
    /// <summary>
    /// 医保对账服务
    /// </summary>
    public class YbSettlementStmtService
    {
        /// <summary>
        /// 3201 对总帐
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        public async Task<OutputStmtTotal> StmtTotal(FsiRequestHeader<InputStmtTotalRequest> fsiRequest, string ControllerName)
        {
            try
            {
                string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                //入参的就医地区划
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 4);
                //入参的机构代码
                string fixmedins_code = fsiRequest.fixmedins_code;
                if (string.IsNullOrEmpty(fsiadmvs) || string.IsNullOrEmpty(fixmedins_code))
                {
                    throw new Exception($"参数校验失败：入参的就医地区划为空或者机构代码为空！");
                }

                if (string.IsNullOrEmpty(fsiRequest.Input.data.insutype) ||
                    string.IsNullOrEmpty(fsiRequest.Input.data.clr_type) ||
                    string.IsNullOrEmpty(fsiRequest.Input.data.stmt_begndate) ||
                    string.IsNullOrEmpty(fsiRequest.Input.data.stmt_enddate))
                {
                    throw new Exception($"参数校验失败：入参的清算类别|险种类别|对账开始时间|对账结束时间为空！");
                }

                string query = "select  medfee_sumamt, fund_pay_sumamt,acct_pay from  fsi_drugstore_order where  order_canceltype =0 and clr_type=@clr_type and insutype =@insutype and create_time>=@begndate and create_time <=@enddate  and fixmedins_code = @fixmedins_code";
                // 如果退费标志传1 则只对退费的数据 否则对退费和未退费
                if (fsiRequest.Input.data.refd_setl_flag == "1")
                {
                    query += " and order_state = 2";
                }
                else
                {
                    query += " and order_state  in (1,3) ";
                }

                //如果是浙江/安徽 则排除异地
                if ((ControllerName == "FsiApiZheJiangController" && fsiadmvs.Substring(0, 2) == "33") ||
                    ControllerName == "FsiApiAnHuiController")
                {
                    query += " and  SUBSTRING(insuplc_admdvs, 1, 4 )= @fsiadmvs ";
                }

                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@clr_type", fsiRequest.Input.data.clr_type),
                    new MySqlParameter("@insutype", fsiRequest.Input.data.insutype),
                    new MySqlParameter("@begndate", fsiRequest.Input.data.stmt_begndate + " 00:00:00"),
                    new MySqlParameter("@enddate", fsiRequest.Input.data.stmt_enddate + " 23:59:59"),
                    new MySqlParameter("@fixmedins_code", fixmedins_code),
                    new MySqlParameter("@fsiadmvs", fsiadmvs)
                };
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                //错误的内容
                string errorStr = "";
                if (dt.Rows.Count != fsiRequest.Input.data.fixmedins_setl_cnt)
                {
                    errorStr +=
                        $"结算笔数不一致，挡板服务实际结算笔数：{dt.Rows.Count}，入参结算笔数：{fsiRequest.Input.data.fixmedins_setl_cnt}|";
                }

                decimal local_medfee_sumamt = dt.AsEnumerable().Sum(row => row.Field<decimal>("medfee_sumamt"));
                if (local_medfee_sumamt != fsiRequest.Input.data.medfee_sumamt)
                {
                    errorStr +=
                        $"费用总额不一致，挡板服务实际费用总额：{local_medfee_sumamt}，入参费用总额额：{fsiRequest.Input.data.medfee_sumamt}|";
                }

                decimal local_fund_pay_sumamt = dt.AsEnumerable().Sum(row => row.Field<decimal>("fund_pay_sumamt"));
                if (local_fund_pay_sumamt != fsiRequest.Input.data.fund_pay_sumamt)
                {
                    errorStr +=
                        $"基金支付总额不一致，挡板服务实际基金支付总额：{local_fund_pay_sumamt}，入参基金支付总额：{fsiRequest.Input.data.fund_pay_sumamt}|";
                }

                decimal local_acct_pay = dt.AsEnumerable().Sum(row => row.Field<decimal>("acct_pay"));
                if (local_acct_pay != fsiRequest.Input.data.acct_pay)
                {
                    errorStr += $"账户支付总额不一致，挡板服务实际账户支付总额：{local_acct_pay}，入参账户支付总额：{fsiRequest.Input.data.acct_pay}|";
                }

                if (errorStr == "")
                {
                    var output = new OutputStmtTotal
                    {
                        stmtinfo = new DataStmtTotal
                        {
                            setl_optins = fsiRequest.insuplc_admdvs,
                            stmt_rslt_dscr = "本次对账结果为平",
                            stmt_rslt = "0"
                        }
                    };
                    return output;
                }
                else
                {
                    errorStr =
                        $"根据传入的机构代码【{fsiRequest.fixmedins_code}】清算类别【{fsiRequest.Input.data.clr_type}】险种类别【{fsiRequest.Input.data.insutype}】对账开始时间【{fsiRequest.Input.data.stmt_begndate}】对账结束时间【{fsiRequest.Input.data.stmt_enddate}】对账不平！对账不平内容:{errorStr}";
                    var output = new OutputStmtTotal
                    {
                        stmtinfo = new DataStmtTotal
                        {
                            setl_optins = fsiRequest.insuplc_admdvs,
                            stmt_rslt_dscr = errorStr,
                            stmt_rslt = "1"
                        }
                    };
                    return output;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[YbSettlementStmtService.StmtTotal]对总帐异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        /// <summary>
        /// 3202 对明细账
        /// </summary>
        /// <param name="fsiRequest">入参</param>
        /// <param name="ControllerName">来源控制器名称</param>
        /// <returns>出参</returns>
        public async Task<OutputStmtDetail> StmtDetail(FsiRequestHeader<InputStmtDetailRequest> fsiRequest, string ControllerName)
        {
            try
            {
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 4);
                string fixmedins_code = fsiRequest.fixmedins_code;
                string connectionString =
                    $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                DateTime dateTime = DateTime.Now;
                string txtfile = fsiRequest.Input.data.file_qury_no;
                //验证文件路径是否存在
                if (!File.Exists(txtfile))
                {
                    throw new Exception($"根据传入的文件路径{txtfile}查询文件不存在！");
                }

                // 读取txt文件的内容，按照tab分割每一行
                string[] lines = File.ReadAllLines(txtfile);
                string query = "select  order_state,setl_id,mdtrt_id,psn_no,msgid, medfee_sumamt, fund_pay_sumamt,acct_pay from  fsi_drugstore_order where  order_canceltype =0  and order_state !=0 and clr_type=@clr_type and create_time>=@begndate and create_time <=@enddate  and fixmedins_code = @fixmedins_code";
                //如果是浙江/安徽 则排除异地
                if ((ControllerName == "FsiApiZheJiangController" && fsiadmvs.Substring(0, 2) == "33") || ControllerName == "FsiApiAnHuiController")
                {
                    query += " and  SUBSTRING(insuplc_admdvs, 1, 4 )= @fsiadmvs ";
                }

                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@clr_type", fsiRequest.Input.data.clr_type),
                    new MySqlParameter("@begndate", fsiRequest.Input.data.stmt_begndate + " 00:00:00"),
                    new MySqlParameter("@enddate", fsiRequest.Input.data.stmt_enddate + " 23:59:59"),
                    new MySqlParameter("@fixmedins_code", fixmedins_code),
                    new MySqlParameter("@fsiadmvs", fsiadmvs)
                };
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                // 创建一个列表来存储分割后的行
                List<string[]> allLines = new List<string[]>();
                try
                {
                    // 调整字段顺序，原来的顺序是：结算ID, 就诊ID, 人员编号, 医疗费总额, 基金支付总额, 个人账户支出, 退费结算标志
                    // 新的顺序应该是：人员编号, 就诊ID, 结算ID, 发送方报文ID, 对账结果, 退费结算标志, 备注
                    foreach (var line in lines)
                    {
                        string[] columns = line.Split('\t');
                        string setl_id = columns[0];
                        string mdtrt_id = columns[1];
                        string psn_no = columns[2];
                        decimal medfee_sumamt = decimal.Parse(columns[3]);
                        decimal fund_pay_sumamt = decimal.Parse(columns[4]);
                        decimal acct_pay = decimal.Parse(columns[5]);
                        string fund_pay_flag = columns[6];
                        if (string.IsNullOrEmpty(setl_id) || setl_id == "null" || string.IsNullOrEmpty(mdtrt_id) || mdtrt_id == "null" || string.IsNullOrEmpty(psn_no) || psn_no == "null")
                        {
                            /*暂时不将部分为null的数据返回给客户端*/
                            /*List<string> newColumns = new List<string>
                            {
                                columns[2], // 人员编号
                                columns[1], // 就诊ID
                                columns[0], // 结算ID
                                "null", // 发送方报文ID
                                "1", // 对账结果
                                columns[6], // 退费结算标志
                                "结算ID|就诊ID|人员编号存在为空或为null的情况", // 备注
                                columns[3], // 医疗费总额
                                columns[4], // 基金支付总额
                                columns[5] // 个人账户支出
                            };
                            allLines.Add(newColumns.ToArray());*/
                        }
                        else
                        {
                            DataRow[] foundRows = dt.Select($"setl_id='{setl_id}' and mdtrt_id='{mdtrt_id}' and psn_no='{psn_no}'");
                            if (foundRows.Length == 0)
                            {
                                // 处理没有找到任何行的情况
                                List<string> newColumns = new List<string>
                                {
                                    columns[2], // 人员编号
                                    columns[1], // 就诊ID
                                    columns[0], // 结算ID
                                    "null", // 发送方报文ID
                                    "1", // 对账结果
                                    columns[6], // 退费结算标志
                                    "挡板服务无此结算记录", // 备注
                                    columns[3], // 医疗费总额
                                    columns[4], // 基金支付总额
                                    columns[5] // 个人账户支出
                                };
                                allLines.Add(newColumns.ToArray());
                            }
                            else if (foundRows.Length > 1)
                            {
                                // 处理找到多于一行的情况
                                List<string> newColumns = new List<string>
                                {
                                    columns[2], // 人员编号
                                    columns[1], // 就诊ID
                                    columns[0], // 结算ID
                                    "null", // 发送方报文ID
                                    "1", // 对账结果
                                    columns[6], // 退费结算标志
                                    $"挡板服务查询到了{foundRows.Length}条未被冲正的记录", // 备注
                                    columns[3], // 医疗费总额
                                    columns[4], // 基金支付总额
                                    columns[5] // 个人账户支出
                                };
                                allLines.Add(newColumns.ToArray());
                            }
                            else
                            {
                                // 处理找到一行的情况
                                DataRow row = foundRows[0];
                                if (decimal.Parse(row["medfee_sumamt"].ToString()) != medfee_sumamt || decimal.Parse(row["fund_pay_sumamt"].ToString()) != fund_pay_sumamt || decimal.Parse(row["acct_pay"].ToString()) != acct_pay)
                                {
                                    List<string> newColumns = new List<string>
                                    {
                                        columns[2], // 人员编号
                                        columns[1], // 就诊ID
                                        columns[0], // 结算ID
                                        row["msgid"].ToString(), // 发送方报文ID
                                        "1", // 对账结果
                                        columns[6], // 退费结算标志
                                        $"金额不符！费用总额：{row["medfee_sumamt"]}|{medfee_sumamt} 基金总额：{row["fund_pay_sumamt"]}|{fund_pay_sumamt} 个账支付：{row["acct_pay"]}|{acct_pay}", // 备注
                                        row["medfee_sumamt"].ToString(), // 医疗费总额
                                        row["fund_pay_sumamt"].ToString(), // 基金支付总额
                                        row["acct_pay"].ToString() // 个人账户支出
                                    };
                                    allLines.Add(newColumns.ToArray());
                                }
                                else
                                {
                                    if (fund_pay_flag == "1" && row["order_state"].ToString() != "3")
                                    {
                                        List<string> newColumns = new List<string>
                                        {
                                            columns[2], // 人员编号
                                            columns[1], // 就诊ID
                                            columns[0], // 结算ID
                                            row["msgid"].ToString(), // 发送方报文ID
                                            "1", // 对账结果
                                            columns[6], // 退费结算标志
                                            $"退费状态不符，挡板服务结算记录不为已退费，但退费标志为1", // 备注
                                            row["medfee_sumamt"].ToString(), // 医疗费总额
                                            row["fund_pay_sumamt"].ToString(), // 基金支付总额
                                            row["acct_pay"].ToString() // 个人账户支出
                                        };
                                        allLines.Add(newColumns.ToArray());
                                    }
                                    else
                                    {
                                        List<string> newColumns = new List<string>
                                        {
                                            columns[2], // 人员编号
                                            columns[1], // 就诊ID
                                            columns[0], // 结算ID
                                            row["msgid"].ToString(), // 发送方报文ID
                                            "0", // 对账结果
                                            columns[6], // 退费结算标志
                                            $"对账成功", // 备注
                                            row["medfee_sumamt"].ToString(), // 医疗费总额
                                            row["fund_pay_sumamt"].ToString(), // 基金支付总额
                                            row["acct_pay"].ToString() // 个人账户支出
                                        };
                                        allLines.Add(newColumns.ToArray());
                                    }
                                }
                                // 从 DataTable 中移除已经处理过的行
                                dt.Rows.Remove(row);
                            }
                        }
                    }
                    // 将 DataTable 中剩余的行数据添加到 allLines 中
                    foreach (DataRow row in dt.Rows)
                    {
                        //处理退费标志 对应表内此纪录的状态
                        string refundSettlementFlag = row["order_state"].ToString() == "1" ? "0" : row["order_state"].ToString() == "3" ? "1" : "0";
                        List<string> newColumns = new List<string>
                        {
                            row["psn_no"].ToString(), // 人员编号
                            row["mdtrt_id"].ToString(), // 就诊ID
                            row["setl_id"].ToString(), // 结算ID
                            row["msgid"].ToString(), // 发送方报文ID
                            "1", // 对账结果
                            refundSettlementFlag, // 退费结算标志
                            "挡板服务多出结算记录", // 备注
                            row["medfee_sumamt"].ToString(), // 医疗费总额
                            row["fund_pay_sumamt"].ToString(), // 基金支付总额
                            row["acct_pay"].ToString() // 个人账户支出
                        };
                        allLines.Add(newColumns.ToArray());
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception($"对明细账失败：明细数据对比服务端数据时发生异常!错误内容：{ex.Message}");
                }

                // 创建一个新的文件路径
                string newFilePath = Path.Combine(Path.GetDirectoryName(txtfile), "result.txt");

                // 将处理后的数据写入新的txt文件，按照tab分割每一列
                using (StreamWriter writer = new StreamWriter(newFilePath))
                {
                    foreach (var line in allLines)
                    {
                        writer.WriteLine(string.Join("\t", line));
                    }
                }

                // 创建压缩包的路径
                string zipFilePath = Path.Combine(Path.GetDirectoryName(txtfile),
                    "FSI" + Path.GetFileNameWithoutExtension(txtfile) + "-" + dateTime.ToString("yyyyMMddHHmmssfff") +
                    ".zip");

                // 对新创建的txt文件进行压缩
                using (ZipArchive archive = ZipFile.Open(zipFilePath, ZipArchiveMode.Create))
                {
                    archive.CreateEntryFromFile(newFilePath, Path.GetFileName(newFilePath), CompressionLevel.Optimal);
                }

                // 删除写入的txt文件
                File.Delete(newFilePath);

                var output = new OutputStmtDetail
                {
                    fileinfo = new OutputFileUpload
                    {
                        filename = Path.GetFileName(zipFilePath),
                        file_qury_no = zipFilePath
                    },
                };
                return output;
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[YbSettlementStmtService.StmtDetail]对明细账异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
        /// <summary>
        /// 1505 异地对总账（浙江省）
        /// </summary>
        /// <param name="fsiRequest"></param>
        /// <param name="ControllerName"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<Tuple<OutputTransBeginMedicine1505, string>> CrossRegionStmtTotal(FsiRequestHeader<InputTransBeginMedicine1505> fsiRequest, string ControllerName)
        {
            try
            {
                string connectionString =
                    $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                //入参的就医地区划
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 4);
                //入参的机构代码
                string fixmedins_code = fsiRequest.fixmedins_code;
                //入参的异地对账数据列表
                List<DataTransBeginMedicine1505Request> dataTransBeginMedicine1505Request = fsiRequest.Input.data;
                List<DataTransBeginMedicine1505> dataTransBeginMedicine1505 = new List<DataTransBeginMedicine1505>();
                if (string.IsNullOrEmpty(fsiadmvs) || string.IsNullOrEmpty(fixmedins_code) ||
                    dataTransBeginMedicine1505Request.Count == 0)
                {
                    throw new Exception($"参数校验失败：入参的就医地区划为空或者机构代码为空或传入的对账数据列表为空！");
                }

                int yes = 0;
                int no = 0;
                string query;

                foreach (var item in dataTransBeginMedicine1505Request)
                {
                    string errorStr = "";
                    query =
                        "select  medfee_sumamt, fund_pay_sumamt,acct_pay from  fsi_drugstore_order where   fixmedins_code = @fixmedins_code and mdtrtarea_admvs = @mdtrtarea_admvs and insuplc_admdvs = @insuplc_admdvs and psn_no=@psn_no and setl_id = @setl_id and mdtrt_id =@mdtrt_id ";
                    var parameter = new List<MySqlParameter>
                    {
                        new MySqlParameter("@fixmedins_code", item.fixmedinsNo),
                        new MySqlParameter("@mdtrtarea_admvs", item.mdtrtareaAdmdvs),
                        new MySqlParameter("@insuplc_admdvs", item.insuplcAdmdvs),
                        new MySqlParameter("@psn_no", item.psnNo),
                        new MySqlParameter("@setl_id", item.setlSn),
                        new MySqlParameter("@mdtrt_id", item.mdtrtSeq)
                    };
                    DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                    if (dt.Rows.Count == 0)
                    {
                        errorStr += $"结算记录不存在，人员编号：{item.psnNo}，结算ID：{item.setlSn}，就诊ID：{item.mdtrtSeq}";
                    }
                    else
                    {
                        if (item.medfeeSumamt != decimal.Parse(dt.Rows[0]["medfee_sumamt"].ToString()))
                        {
                            errorStr +=
                                $"费用总额不一致，挡板服务实际费用总额：{decimal.Parse(dt.Rows[0]["medfee_sumamt"].ToString())}，入参费用总额额：{item.medfeeSumamt}|";
                        }

                        if (item.optinsPaySumamt != (decimal.Parse(dt.Rows[0]["fund_pay_sumamt"].ToString()) +
                                                     decimal.Parse(dt.Rows[0]["acct_pay"].ToString())))
                        {
                            errorStr +=
                                $"经办机构支付总额不一致，挡板服务经办机构支付总额：{decimal.Parse(dt.Rows[0]["fund_pay_sumamt"].ToString()) + decimal.Parse(dt.Rows[0]["acct_pay"].ToString())}，入参经办机构支付总额：{item.optinsPaySumamt}|";
                        }
                    }

                    if (string.IsNullOrEmpty(errorStr))
                    {
                        query =
                            "update fsi_drugstore_order set stmt_type =1 where  fixmedins_code = @fixmedins_code and mdtrtarea_admvs = @mdtrtarea_admvs and insuplc_admdvs = @insuplc_admdvs and psn_no=@psn_no and setl_id = @setl_id and mdtrt_id =@mdtrt_id ";
                        await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
                        DataTransBeginMedicine1505 Medicine = new DataTransBeginMedicine1505
                        {
                            fixmedinsNo = fixmedins_code,
                            insuplcAdmdvs = item.insuplcAdmdvs,
                            mdtrtSeq = item.mdtrtSeq,
                            mdtrtareaAdmdvs = item.mdtrtareaAdmdvs,
                            psnNo = item.psnNo,
                            setlSn = item.setlSn,
                            succFlag = "1"
                        };
                        dataTransBeginMedicine1505.Add(Medicine);
                        yes = yes + 1;
                    }
                    else
                    {
                        query =
                            "update fsi_drugstore_order set stmt_type =2 where  fixmedins_code = @fixmedins_code and mdtrtarea_admvs = @mdtrtarea_admvs and insuplc_admdvs = @insuplc_admdvs and psn_no=@psn_no and setl_id = @setl_id and mdtrt_id =@mdtrt_id ";
                        await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
                        DataTransBeginMedicine1505 Medicine = new DataTransBeginMedicine1505
                        {
                            fixmedinsNo = fixmedins_code,
                            insuplcAdmdvs = item.insuplcAdmdvs,
                            mdtrtSeq = item.mdtrtSeq,
                            mdtrtareaAdmdvs = item.mdtrtareaAdmdvs,
                            psnNo = item.psnNo,
                            setlSn = item.setlSn,
                            succFlag = "0",
                            prmmsg = errorStr
                        };
                        dataTransBeginMedicine1505.Add(Medicine);
                        no = no + 1;
                    }
                }

                string warnmsg = $"共{dataTransBeginMedicine1505Request.Count}条数据，其中{yes}条数据对账成功，{no}条数据对账失败";
                return Tuple.Create(new OutputTransBeginMedicine1505
                {
                    data = dataTransBeginMedicine1505
                }, warnmsg);
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[YbSettlementStmtService.CrossRegionStmtTotal]异地对总账异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
        /// <summary>
        /// 1812 异地未完成明细账（浙江省）
        /// </summary>
        /// <param name="fsiRequest"></param>
        /// <param name="ControllerName"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<OutputTransBeginMedicine1812> CrossRegionStmtUnfinishedDetails(
            FsiRequestHeader<InputTransBeginMedicine1812> fsiRequest, string ControllerName)
        {
            try
            {
                string connectionString =
                    $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
                //入参的就医地区划
                string fsiadmvs = fsiRequest.mdtrtarea_admvs.Substring(0, 4);
                //入参的机构代码
                string fixmedinsNo = fsiRequest.Input.data.fixmedinsNo;
                //入参的数据版本（虚拟成分页页码）
                int loclDataVer = fsiRequest.Input.data.loclDataVer;
                //开始时间
                DateTime begndate = DateTime.ParseExact(fsiRequest.Input.data.begndate, "yyyyMMdd",
                    System.Globalization.CultureInfo.InvariantCulture);
                ;
                //开始时间
                DateTime enddate = DateTime.ParseExact(fsiRequest.Input.data.enddate, "yyyyMMdd",
                    System.Globalization.CultureInfo.InvariantCulture);
                ;
                if (string.IsNullOrEmpty(fsiadmvs) || string.IsNullOrEmpty(fixmedinsNo))
                {
                    throw new Exception($"参数校验失败：入参的就医地区划为空或者机构代码为空！");
                }

                if (loclDataVer < 0)
                {
                    throw new Exception($"参数校验失败：省内数据版本号(虚拟成分页页码)为负数！");
                }

                if (begndate != enddate)
                {
                    throw new Exception($"参数校验失败：开始时间与结束时间不一致！");
                }

                List<DataTransBeginMedicine1812> dataTransBeginMedicine1812 = new List<DataTransBeginMedicine1812>();
                string query =
                    "select order_state, medfee_sumamt, fund_pay_sumamt,acct_pay,fund_pay_sumamt+acct_pay as optinsPaySumamt, psn_cash_pay,setl_id," +
                    "mdtrt_id,psn_no,fixmedins_code,msgid,mdtrtarea_admvs,insuplc_admdvs,med_type,create_time,update_time from  fsi_drugstore_order" +
                    " where  fixmedins_code = @fixmedins_code  and order_state  in (1,3) and order_canceltype =0  and create_time>=@begndate and create_time<=@enddate and SUBSTRING(insuplc_admdvs, 1, 4 ) <> @fsiadmvs and stmt_type in (0,2) limit @page,100 ";
                var parameter = new List<MySqlParameter>
                {
                    new MySqlParameter("@fixmedins_code", fixmedinsNo),
                    new MySqlParameter("@fsiadmvs", fsiadmvs),
                    new MySqlParameter("@begndate", begndate),
                    new MySqlParameter("@enddate", enddate.AddDays(1)),
                    new MySqlParameter("@page", loclDataVer)
                };
                DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
                if (dt.Rows.Count == 0)
                {
                    return new OutputTransBeginMedicine1812
                    {
                        data = dataTransBeginMedicine1812
                    };
                }

                foreach (DataRow row in dt.Rows)
                {
                    DateTime createTime = Convert.ToDateTime(row["create_time"]);
                    string trnsType = row["order_state"].ToString() == "3" ? "1" : "0";
                    DataTransBeginMedicine1812 medicine = new DataTransBeginMedicine1812
                    {
                        mdtrtareaAdmdvs = row["mdtrtarea_admvs"].ToString(),
                        insuplcAdmdvs = row["insuplc_admdvs"].ToString(),
                        medfeeSumamt = decimal.Parse(row["medfee_sumamt"].ToString()),
                        psnCashpay = decimal.Parse(row["psn_cash_pay"].ToString()),
                        optinsPaySumamt = decimal.Parse(row["optinsPaySumamt"].ToString()),
                        mdtrtSetlTime = createTime.ToString("yyyyMMddHHmmss"),
                        trnsType = trnsType,
                        mdtrtSeq = row["mdtrt_id"].ToString(),
                        setlSn = row["setl_id"].ToString(),
                        loclDataVer = loclDataVer + 1,
                        medType = row["med_type"].ToString(),
                        fixmedinsNo = row["fixmedins_code"].ToString(),
                        senderMsgId = row["msgid"].ToString(),
                        recerMsgId = row["msgid"].ToString(),
                        psnNo = row["psn_no"].ToString(),
                    };
                    dataTransBeginMedicine1812.Add(medicine);
                }

                return new OutputTransBeginMedicine1812
                {
                    data = dataTransBeginMedicine1812
                };
            }
            catch (Exception ex)
            {
                LogHelper.Error(
                    $"[YbSettlementStmtService.CrossRegionStmtUnfinishedDetails]异地未完成明细账异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
                throw;
            }
        }
        
    }
}
